if exists(select 1
		  from sysobjects
		  where name = 'Candidat_TypeHistory')
  drop table Candidat_TypeHistory
go

create table Candidat_TypeHistory
(
  Id int identity(1,1) not null,
  CandidatID int not null,
  [Date] date not null,
  CandidateTypeId int not null,
  IsCandidate bit not null,
  IsContactPerson bit not null
)  
go

alter table Candidat_TypeHistory add constraint PK_Candidat_TypeHistory_ID primary key(Id) 
go


alter table Candidat_TypeHistory add constraint FK_Candidat_TypeHistory_Candidates_CandidatID 
      foreign key (CandidatID) references Candidates(Id) on delete cascade
go

alter table Candidat_TypeHistory add constraint FK_Candidat_TypeHistory_Candidate_Types_CandidateTypeId 
      foreign key (CandidateTypeId) references Candidate_Types(Id) on delete cascade
go

insert into Candidat_TypeHistory(CandidatID, [Date], CandidateTypeId, IsCandidate, IsContactPerson)		
select Id, isnull(cast(DLC as date),getdate()), CandidateTypeId, IsCandidate, IsContactPerson
from Candidates
go


alter TRIGGER [dbo].[Candidates_ITrig] ON [dbo].[Candidates] AFTER Insert
AS 
BEGIN
  if @@rowcount = 0 return
  
  insert into Candidat_TypeHistory(CandidatID, [Date], CandidateTypeId, IsCandidate, IsContactPerson)		
  select i.Id, cast(getdate() as date), i.CandidateTypeId, i.IsCandidate, i.IsContactPerson
  from inserted i
   
  update c set
    c.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Candidates c
  join inserted i on c.Id = i.Id
end

GO


ALTER TRIGGER [dbo].[Candidates_UTrig] ON [dbo].[Candidates] AFTER UPDATE
AS 
BEGIN
  if @@rowcount = 0 return	 

  insert into Candidat_TypeHistory(CandidatID, [Date], CandidateTypeId, IsCandidate, IsContactPerson)		
  select i.Id, cast(getdate() as date), i.CandidateTypeId, i.IsCandidate, i.IsContactPerson
  from inserted i
  join deleted d on d.Id = i.Id
  where isnull(i.CandidateTypeId,0) <> isnull(d.CandidateTypeId,0) or
 		isnull(i.IsCandidate,-1) <> isnull(d.IsCandidate,-1) or
		isnull(i.IsContactPerson,-1) <> isnull(d.IsContactPerson,-1) 
  
  update c set
    c.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Candidates c
  join inserted i on c.Id = i.Id
  join deleted d on d.Id = i.Id
  where (isnull(i.ApproveOnPost,-1) <> isnull(d.ApproveOnPost,-1) or
		 isnull(i.ManagerId,0) <> isnull(d.ManagerId,0) or
		 isnull(i.SexID,0) <> isnull(d.SexID,0) or
		 isnull(i.CarID,0) <> isnull(d.CarID,0) or
		 isnull(i.SalaryRangeID,0) <> isnull(d.SalaryRangeID,0) or
		 isnull(i.Position_CompareImportantID,0) <> isnull(d.Position_CompareImportantID,0) or
		 isnull(i.Territory_CompareImportantID,0) <> isnull(d.Territory_CompareImportantID,0) or
		 isnull(i.SalaryRange_CompareImportantID,0) <> isnull(d.SalaryRange_CompareImportantID,0) or
		 isnull(i.TerritoryID,0) <> isnull(d.TerritoryID,0) or
		 isnull(i.IsCandidate,-1) <> isnull(d.IsCandidate,-1) or
		 isnull(i.IsContactPerson,-1) <> isnull(d.IsContactPerson,-1) or
	     isnull(i.DocumentSum,0) <> isnull(d.DocumentSum,0) or
	     isnull(i.CandidateTypeId,0) <> isnull(d.CandidateTypeId,0) or
		 isnull(d.DateOfBirthDay,'1 jan 2000') <> isnull(i.DateOfBirthDay,'1 jan 2000') or
		 isnull(d.EnterDate,'1 jan 2000') <> isnull(i.EnterDate,'1 jan 2000') or
	     isnull(i.DocumnetNumber,'') <> isnull(d.DocumnetNumber,'') or
		 isnull(i.LastName,'') <> isnull(d.LastName,'') or
		 isnull(i.Adress,'') <> isnull(d.Adress,'') or
		 isnull(i.PrePaid,'') <> isnull(d.PrePaid,'') or
		 isnull(i.Education,'') <> isnull(d.Education,'') or
		 isnull(i.ResumeName,'') <> isnull(d.ResumeName,'') or
		 isnull(i.EducationType,'') <> isnull(d.EducationType,'') or
		 isnull(i.OnlyTradeMarks,'') <> isnull(d.OnlyTradeMarks,'') or
		 update(Characteristic) or
		 update(OtherReqirments) or
		 update(CommentForHistory)) and
		isnull(c.DLC,dateadd(day,-1,getdate())) <> convert(datetime,convert(varchar,getdate(),102))
end
GO


